-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE GET_DATA_REPORT 
	-- Add the parameters for the stored procedure here
	@fromdate date, 
	@todate date,
	@usrID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT
		NGAY_THU_CHI,
		NHAN_VIEN,
		SUM(TIEN_PHONG) AS TIEN_PHONG,
		SUM(TIEN_DIEN) AS TIEN_DIEN,
		SUM(TIEN_NUOC) AS TIEN_NUOC,
		SUM(TIEN_GUI_XE) AS TIEN_GUI_XE,
		SUM(TIEN_PHU_THU) AS TIEN_PHU_THU,
		SUM(CHI) AS TIEN_CHI
	FROM (
		SELECT 
			ISNULL(THU_TIEN.NGAY_THU, CHI_TIEU.NGAY_CHI) AS NGAY_THU_CHI,
			USR.FULL_NAME AS NHAN_VIEN,
			ISNULL(THU_TIEN.TIEN_PHONG,0) AS TIEN_PHONG,
			(ISNULL(CHI_SO_DIEN_SAU,0) - ISNULL(CHI_SO_DIEN_TRUOC,0))*(ISNULL(DON_GIA_DIEN,0)) TIEN_DIEN,
			(ISNULL(CHI_SO_NUOC_SAU,0) - ISNULL(CHI_SO_NUOC_TRUOC,0))*(ISNULL(DON_GIA_NUOC,0)) TIEN_NUOC,
			ISNULL(TIEN_GUI_XE,0) TIEN_GUI_XE,
			ISNULL(TIEN_PHU_THU,0) TIEN_PHU_THU,
			ISNULL(SO_TIEN,0) CHI
		FROM THU_TIEN 
			FULL JOIN CHI_TIEU ON CONVERT(varchar, NGAY_THU) + '|' + CONVERT(varchar, NGUOI_THU) = CONVERT(varchar, NGAY_CHI) + '|' + CONVERT(varchar, NGUOI_CHI) 
			JOIN USR ON (THU_TIEN.NGUOI_THU = USR.ID OR CHI_TIEU.NGUOI_CHI = USR.ID)
		WHERE CAST(THU_TIEN.NGAY_THU AS DATE) BETWEEN CAST(@fromdate AS DATE)
			AND CAST(@todate AS DATE)
			AND (@usrID = -1 OR THU_TIEN.NGUOI_THU = @usrID OR CHI_TIEU.NGUOI_CHI = @usrID)
		) AS tmp
	GROUP BY NGAY_THU_CHI, NHAN_VIEN
	ORDER BY NGAY_THU_CHI
END
GO
